package ee.db;

import ee.model.Assignment;
import ee.model.Project;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;

import static org.apache.commons.lang3.StringUtils.isEmpty;

@Service
public class ProjectService {
  @Autowired
  JdbcTemplate jdbc;
  @Autowired
  UserService userService;


  public Project getProject(String projectName, String username) {
    int userId =   userService.getUserId(username);
    String sql = "SELECT * FROM projects where projectname = '" + projectName + "' AND userid = '" + userId + "' AND todo IS NOT NULL";
    List<Assignment> assignments = jdbc.query(sql, new AssignmentMapper());
    Project project = new Project();
    project.setAssignments(assignments);
    project.setProjectName(projectName);
    return project;
  }

  public String getNewAssignmentId(String projectName, String username) {
    int userId = userService.getUserId(username);
    jdbc.execute("DELETE FROM projects WHERE userId = " + userId + " AND todo IS NULL");
    jdbc.execute("INSERT INTO projects (projectName, userId) VALUES ('" + projectName + "', " + userId + ")");
    int newId = jdbc.queryForInt("SELECT id FROM PROJECTS WHERE projectname = '" + projectName + "' AND userid = '" + userId + "' " +
      " AND todo IS NULL LIMIT 1");
    return String.valueOf(newId);
  }

  public void saveAssignment(Assignment assignment) {
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yy");
    String date = assignment.getDeadline().replaceAll("/", "-");
    String sql = "UPDATE projects SET " +
      "todo = '" + assignment.getTopic() + "'," +
      (isEmpty(assignment.getDeadline()) ? "" : "deadline = STR_TO_DATE('" +  date + "','%d-%m-%Y'),") +
      "comments = '" + assignment.getComments() + "'," +
      "priority = " + Integer.valueOf(assignment.getPriority()) + "," +
      "status = '" + assignment.getStatus() + "'," +
      "tags = '" + assignment.getTags() + "'" +
      "WHERE id = " + Integer.valueOf(assignment.getId()) + "";
    jdbc.execute(sql);
  }

  public void deleteAssignment(String id) {
    jdbc.execute("DELETE FROM projects WHERE id = " + id);
  }

  public void changeState(String id, String state) {
    jdbc.execute("UPDATE projects SET status = '" + state + "' where id = " + id);
  }

  public Object[] getProjectNames(String username) {
    return jdbc.queryForList("SELECT DISTINCT projectName from projects WHERE userId = " + userService.getUserId(username)).toArray();
  }


  class AssignmentMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int i) throws SQLException {
      Assignment assignment = new Assignment();
      assignment.setTopic(rs.getString("todo"));
      assignment.setStatus(rs.getString("status"));
      assignment.setComments(rs.getString("comments"));
      assignment.setTags(rs.getString("tags"));
      assignment.setPriority(String.valueOf(rs.getInt("priority")));
      assignment.setId(String.valueOf(rs.getInt("id")));

      if(rs.getString("deadline") == null || isEmpty(rs.getString("deadline"))){
        assignment.setDeadline("");
      }
      else {
        DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        assignment.setDeadline(dateFormat.format(rs.getDate("deadline")));
      }
      return assignment;
    }
  }
}
